/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.CoursePupil;
import vn.com.pps.entities.CoursePupilDetail;

/**
 *
 * @author Dai
 */
public class CoursePupilDAO extends BaseDAO implements ICoursePupilDAO {

    @Override
    public int insert(CoursePupil coursePupil) {
        String sql = "INSERT INTO tblcoursepupils(ID,CourseID,PupilID,Status,Note,CreatedOn,LastModified) VALUES(?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, coursePupil.getCourseID(), coursePupil.getPupilID(), coursePupil.getStatus(),
            coursePupil.getNote(), coursePupil.getRegistedOn(), coursePupil.getLastModified()
        };
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CourseID", Types.INTEGER));
        psc.addParameter(new SqlParameter("PupilID", Types.INTEGER));
        psc.addParameter(new SqlParameter("Status", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(CoursePupil coursePupil) {
        System.out.println("@update::");
        System.out.println("Status:"+coursePupil.getStatus());
        System.out.println("CourseID="+coursePupil.getId());
        String sql = "UPDATE tblcoursepupils SET Status=?,Note=?,LastModified=? WHERE ID=?";
        Object[] params = new Object[]{
            coursePupil.getStatus(),
            coursePupil.getNote(),
            coursePupil.getLastModified(), coursePupil.getId()
        };
        
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sql);
        psc.addParameter(new SqlParameter("Status", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Note", Types.VARCHAR));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
        return coursePupil.getId();
    }

    @Override
    public int delete(int id) {
        String sql = "DELETE FROM tblcoursepupils WHERE ID=" + id;
        getJdbcTemplate().execute(sql);
        return id;
    }

    @Override
    public CoursePupilDetail getCoursePupil(Integer id) {
        String sqlQuery = "SELECT  TblCoursePupils.ID,TblCoursePupils.pupilID,TblCoursePupils.Status,TblCoursePupils.Note,TblCoursePupils.CreatedOn,TblCoursePupils.LastModified,tblpupils.Firstname,tblpupils.Lastname,tblpupils.Mobile,TblCoursePupils.CourseID  FROM TblCoursePupils,tblpupils WHERE TblCoursePupils.PupilID=tblpupils.ID AND TblCoursePupils.ID =" + id;
        return (CoursePupilDetail) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CoursePupilDetail coursePupilDetail = new CoursePupilDetail();
                coursePupilDetail.setFirstName(rs.getString("Firstname"));
                coursePupilDetail.setLastName(rs.getString("Lastname"));
                coursePupilDetail.setMobile(rs.getString("Mobile"));
                coursePupilDetail.setCourseID(rs.getInt("CourseID"));
                coursePupilDetail.setPupilID(rs.getInt("pupilID"));
                coursePupilDetail.setId(rs.getInt("ID"));
                coursePupilDetail.setRegistedOn(rs.getTimestamp("CreatedOn"));
                coursePupilDetail.setLastModified(rs.getTimestamp("LastModified"));
                coursePupilDetail.setStatus(rs.getString("Status"));
                coursePupilDetail.setNote(rs.getString("Note"));
                return coursePupilDetail;
            }
        });
    }

    @Override
    public Page<CoursePupilDetail> getCoursePupils(int pageNo, int pageSize, Integer courseID) {
        String sqlQuery = "SELECT  TblCoursePupils.ID,TblCoursePupils.pupilID,TblCoursePupils.Status,TblCoursePupils.Note,TblCoursePupils.CreatedOn,TblCoursePupils.LastModified,tblpupils.Firstname,tblpupils.Lastname,tblpupils.Mobile,TblCoursePupils.CourseID  FROM TblCoursePupils,tblpupils WHERE TblCoursePupils.Status='A' AND TblCoursePupils.PupilID=tblpupils.ID AND TblCoursePupils.CourseID =" + courseID;
        System.out.println("sqlQuery:"+sqlQuery);
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCoursePupils WHERE CourseID=" + courseID;
        PaginationHelper<CoursePupilDetail> ph = new PaginationHelper<CoursePupilDetail>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<CoursePupilDetail>() {
                    @Override
                    public CoursePupilDetail mapRow(ResultSet rs, int i) throws SQLException {
                        CoursePupilDetail coursePupilDetail = new CoursePupilDetail();
                        coursePupilDetail.setFirstName(rs.getString("Firstname"));
                        coursePupilDetail.setLastName(rs.getString("Lastname"));
                        coursePupilDetail.setMobile(rs.getString("Mobile"));
                        coursePupilDetail.setCourseID(rs.getInt("CourseID"));
                        coursePupilDetail.setPupilID(rs.getInt("pupilID"));
                        coursePupilDetail.setId(rs.getInt("ID"));
                        coursePupilDetail.setRegistedOn(rs.getTimestamp("CreatedOn"));
                        coursePupilDetail.setLastModified(rs.getTimestamp("LastModified"));
                        coursePupilDetail.setStatus(rs.getString("Status"));
                        coursePupilDetail.setNote(rs.getString("Note"));
                        return coursePupilDetail;
                    }
                });
    }

    @Override
    public int getCount(Integer courseID) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCoursePupils WHERE CourseID=" + courseID;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public CoursePupilDetail getCoursePupilDetail(Integer id) {
        String sqlQuery = "SELECT TblCoursePupils.ID,TblCoursePupils.pupilID,tblpupils.Firstname,tblpupils.Lastname,tblpupils.Mobile,tblpupils.Mother,TblCoursePupils.CourseID,TblCoursePupils.CreatedOn,TblCoursePupils.Status  FROM TblCoursePupils,tblpupils WHERE TblCoursePupils.PupilID=tblpupils.ID AND TblCoursePupils.ID=" + id;
        return (CoursePupilDetail) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                CoursePupilDetail coursePupilDetail = new CoursePupilDetail();
                coursePupilDetail.setFirstName(rs.getString("Firstname"));
                coursePupilDetail.setLastName(rs.getString("Lastname"));
                coursePupilDetail.setMobile(rs.getString("Mobile"));
                coursePupilDetail.setCourseID(rs.getInt("CourseID"));
                coursePupilDetail.setPupilID(rs.getInt("pupilID"));
                coursePupilDetail.setId(rs.getInt("ID"));
                coursePupilDetail.setParentName(rs.getString("Mother"));
                coursePupilDetail.setRegistedOn(rs.getTimestamp("CreatedOn"));
                coursePupilDetail.setStatus(rs.getString("Status"));
                return coursePupilDetail;
            }
        });
    }

    @Override
    public int getCountCoursesByPupil(Integer PupilID) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCoursePupils WHERE PupilID=" + PupilID;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public Page<CoursePupilDetail> getCourseByPupils(int pageNo, int pageSize, Integer PupilID) {
        String sqlQuery = "SELECT  TblCoursePupils.CourseID,TblCourses.CourseName,TblCourses.CourseType,TblCoursePupils.Status,TblCourses.Schedule  FROM TblCoursePupils,TblCourses WHERE TblCoursePupils.CourseID=TblCourses.ID AND TblCoursePupils.PupilID =" + PupilID;
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCoursePupils WHERE CourseID=" + PupilID;
        PaginationHelper<CoursePupilDetail> ph = new PaginationHelper<CoursePupilDetail>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<CoursePupilDetail>() {
                    @Override
                    public CoursePupilDetail mapRow(ResultSet rs, int i) throws SQLException {
                        CoursePupilDetail coursePupilDetail = new CoursePupilDetail();
                        coursePupilDetail.setCourseID(rs.getInt("CourseID"));
                        coursePupilDetail.setCourseName(rs.getString("CourseName"));
                        coursePupilDetail.setCourseType(rs.getInt("CourseType"));
                        coursePupilDetail.setStatus(rs.getString("Status"));
                        coursePupilDetail.setSchedule(rs.getString("Schedule"));
                        return coursePupilDetail;
                    }
                });
    }

}
